#!/usr/bin/env python
'''PyLibrarian database model'''

import ConfigParser
from datetime import datetime, timedelta
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.types import *
from sqlalchemy.schema import *
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm.session import sessionmaker

config = ConfigParser.ConfigParser()
config.readfp(open('config.ini'))

engine = create_engine(config.get('backend', 'connection_string'))
session = scoped_session(sessionmaker(autocommit=False,
                                      autoflush=False,
                                      bind=engine))
Base = declarative_base(bind=engine)

class User(Base):
  '''Users table - keeps track of the library users'''
  __tablename__ = 'users'
  id = Column(Integer, primary_key=True)
  name = Column(Unicode(100), index=True)
  username = Column(Unicode(100), unique=True, nullable=False)
  password = Column(Unicode(100))
  street = Column(Unicode(500))
  city = Column(Unicode(100))
  state = Column(Unicode(100))
  zip = Column(Integer)
  phone = Column(Unicode(25))
  email = Column(Unicode(250))
  admin = Column(Boolean, default=False, nullable=False)
  
class ResourceType(Base):
  '''ResourceTypes table - keeps tracks of different types of resources'''
  __tablename__ = 'resource_types'
  id = Column(Integer, primary_key=True)
  description = Column(String(100), unique=True, nullable=False)
  
class Resource(Base):
  '''Resources table - keeps track of the resources in the library'''
  __tablename__ = 'resources'
  id = Column(Integer, primary_key=True)
  isbn = Column(Unicode(50), index=True)               # May be left blank if the resource doesn't have an ISBN
  title = Column(Unicode(500), index=True)
  author = Column(Unicode(500), nullable=False)
  publisher = Column(Unicode(500))
  resource_type_id = Column(ForeignKey('resource_types.id', name='resource_type_id'), nullable=False)
  summary = Column(UnicodeText())
  
class Loan(Base):
  '''Loans table - keeps track of which resources are checked out'''
  __tablename__ = 'loans'
  id = Column(Integer, primary_key=True)
  resource_id = Column(ForeignKey('resources.id', name='resource_id'), nullable=False)
  user_id = Column(ForeignKey('users.id', name='user_id'), nullable=False)
  checkout_date = Column(DateTime, default=datetime.now)
  due_date = Column(DateTime, default=lambda x=None: datetime.now() + timedelta(weeks=config.getint('library', 'checkout_time')))
  returned_date = Column(DateTime)
  
class Hold(Base):
  '''Holds table - keeps track of which resources are on hold'''
  __tablename__ = 'holds'
  id = Column(Integer, primary_key=True)
  resource_isbn = Column(Integer, primary_key=True)
  user_id = Column(ForeignKey('users.id', name='user_id'))
  date = Column(DateTime, default=datetime.now)
  
def addResourceTypes(event, target, bind):
  db = session()
  try:
    types = ['Book',      # A list of the resource types supported by the library
             'Cassette',
             'CD',
             'DVD',
             'VHS']
    for type in types:
      rec = db.query(ResourceType).filter_by(description=type).first()
      if not rec:
        rec = ResourceType(description=type)
        db.add(rec)
        
    db.commit()
  finally:
    db.close()
  
Base.metadata.append_ddl_listener('after-create', addResourceTypes)
Base.metadata.create_all()